The transfer SQL server Objects task is used to transfer one or more SQL server objects to a different database, either on the same or another SQL server instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user-defined functions, etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.
Create another package in the same project and drag a “Transfer SQL Server Objects Task” to the Control Flow. Right-click on the task and then select Edit. In the “Transfer SQL server objects task editor” click on objects to set the different properties for this task as shown below.
Similar to the way you configured the source connection and destination connection for the Transfer Database Task, you need to configure these properties for this task as well. Source Database is the name of the database from where you are copying the objects and the Destination database is the name Of the database to which you are copying the objects to
If you want to enrich your career and become a professional in SQL Server, then visit Tekslate - a global online training platform: "SQL Server Training" This course will help you to achieve excellence in this domain.
Connection:
Source Connection - the source instance
Source Database - the name of the source database
Destination Database - the name of the new database
Destination
Drop objects first - Drop selected objects on the target before copy
Include extended properties - while copying operation also include extended properties of SQL objects being copied
Copy Data - While copying tables, transfer the data of the selected tables as well
Existing Data - Whether to append or replace data
Copy schema - Copy the schema of the objects being copied
Use Collation - Make sure collation of the columns are appropriately set on copied tables
Include Dependent objects - Include all the objects in copy operation which are dependent on selected objects
Destination Copy objects
Copy all objects - Do you want to copy all objects from the source database, if set to false, the next property objects to copy will get enabled.
Objects to copy - With this property, you select types of objects you want to copy.
You can select all objects of one or more types or select a particular version type of object selection will vary.
Security
Copy Database users - whether to include users
Copy Database Roles - Whether to include roles
Copy SQL Server Logins - Whether to include logins
Copy object-level permissions - whether to include object-level permission
Table options
Copy indexes - Whether to include indexes
Copy Triggers - whether to include triggers
Copy Full-Text indexes - Whether to include full-text indexes
Copy all DRI objects - whether to include referential integrity objects
Copy primary keys - whether to include primary keys
Copy foreign keys - Whether to include foreign keys
Generate script in Unicode - whether to create a script in Unicode or--------
Transfer jobs task
The transfer jobs task is used to transfer SQL server agent jobs from one SQL server instance to another. This task gives you an option to copy all the jobs or selected jobs from the source server to the destination server.
Open BIDS Drag a Transfer Jobs Task from the Toolbox to the Control Flow pane as shown below. Right-click on this task and click on edit
Connections
Source connections – specify the connection for the source SQL server instance, if you have already created a connection then you can reuse it here or can create a new one.
Destination Connection - Specify the Connection for the destination SQL Server instance
Jobs
Transfer All Jobs:
If this is set to true it will transfer all jobs. If this is set to False you can select Specific jobs you want to transfer.
Jobs List:
This will be enabled if Transfer All Jobs is set to False. Then you can select Specific Jobs to transfer. See the image below Where I am only Selecting two jobs to transfer to the Destination
Options
If the jobs already exist on the destination. Then you have three Choices, first Fail Task Execution, Second Overwrite the destination job and third Skip the existing job and continue With others.
After the transfer, you can also specify whether to enable or job.
Transfer Logins Task:
Connections
Source connection - Specify the connection for the source SQL server instance.
Logins
Login Transfer: you have three options for this:
Logins List:
This will allow you to select Specific logins if you select Selected Logins For Logins Transfer.
Database List :
This will allow you to select the databases if you select All Logins from Selected Databases for Logins TO Transfer.
Options
If the Logins already exist on the destination you have three choices First Fail Task execution, Second over Write the destination logins, And third skip the existing login and continue with the others.
Checkout Our Frequently Asked SQL Server Interview Questions
Data Source:
Logical Connection between application (SSRS) and databases
Report Server:
Report Server is the core engine that drives Reporting Services. The URL for report Server is https:// Localhost/Report Server.
Report Manager:
Example:
Report Builder:
Is a Simplified end-user or Adhoc reporting tool for building Simple Reports.
TABLE REPORT ITEM
It is used to display data in a Grid format that has a fixed number of columns and
A variable number of rows.
Steps to Configure Table Report Item:
Open Business Intelligence Development ------------>
Select File Menu ------------>
Select New ------------>
Select Project ------------>
Under Business Intelligence Projects Select Report Server Project option to
Build Simple and complex Reports on the development environment. ------------>
Change the location and name of the project ------------>
Click ok ------------>
Press Alt+ Ctrl + L for Solution explorer ------------>
Select Shared data Source and right-click. Select add new data source option
From context Menu or right popup Menu ------------>
Set,
In share Data source editor,
Data source - Remove it as D Source Product Details
Type - Microsoft SQL Server
Connection string – click Edit ------------>
Provider Server Name (localhost) (or). (Or) Host Name (or) IP Address ------------>
Select Adventure Works ------------>
Click ok ------------>
Click ok ------------>
In Solution Explorer, Select Reports, right-click ------------>
Select Add and also select New Item ------------>
In Data Tab, Select new data Set Option and Set,
Data set Name – D Set Product Information
Data Source - Select D Source product Details from the drop-down list
Command Type - Text (SQL Script) ------------>
Click ok ------------>
Select query Designer Option and Add Tables icon on the rare right and Select Tables
(Or)
Write the query in SSMS and copy and paste in Query Designer
Select Product Category. Product Category ID,
Select Product Category. Product Category Name,
Product. Product ID,
Product. Standard Cost,
Product. Size,
Product. Sell start Date,
Product. Reorder point
From product category Inner Join product subcategory. Product category ID =
Product subcategory. Product category ID.
Inner Join product on the product. Product subcategory. Product =
Subcategory ID Subcategory ID
Inner Join Product cost History ON Product. Product ID =
Product cost History. Product ID
Provide the above query to the created data set and click Refresh ------------>
Click ok ------------>
Select View Menu and Select Data Set option or (Alt + Ctrl + D) ------------>
Select the Layout tab to design the Report ------------>
Select tool box (or) (Alt + Ctrl + X) ------------>
Drag and Drop table Report item on the body of the Report ------------>
Drag and Drop Category Name field on to Detail section of the table report [Building (or) ------------>
Mapping the field (or) Columns from data set to Report item] ------------>
Map other fields as well ------------>
In table Report item Select 1 Row and 2 Row ------------>
Press F4 and Set ------------>
Border style - Solid ------------>
Select 1 Row [Header] and Press F4 for properties and set ------------>
Background color - Marron
Text align - Centre
Font Size - 9pt
Color – White
Data Source: Logical Connection between application (SSRS) and databases
Report Server: Report Server is the core engine that drives Reporting Services. The URL for report Server is https:// Localhost/Report Server.
Report Manager:
Example:
Report Builder: Is a Simplified end-user or Adhoc reporting tool for building Simple Reports.
TABLE REPORT ITEM
It is used to display data in a Grid format that has a fixed number of columns and a Variable number of rows.
Steps to Configure Table Report Item:
Open Business Intelligence Development ----------->
Select File Menu ----------->
Select New ----------->
Select Project ----------->
Under Business Intelligence Projects Select Report Server Project option to ----------->
Build Simple and complex Reports on development environment. ----------->
Change the location and name of the project ----------->
Click ok ----------->
Press Alt+ Ctrl + L for Solution explorer ----------->
Select Shared data Source and right-click .Select add new data source option ----------->
From context Menu or right popup Menu ----------->
Set,
In share Data source editor,
Data source - Remove it as D Source Product Details
Type - Microsoft SQL Server Connection string – click Edit ----------->
Provider Server Name (local host) (or). (Or) Host Name (or) IP Address ----------->
Select Adventure Works ----------->
Click ok ----------->
Click ok ----------->
In solution Explorer, Select Reports, right click ----------->
Select Add and also select New Item ----------->
In Data Tab, Select new data Set Option and Set, ----------->
Data set Name – D Set Product Information
Data Source - Select D Source product Details from drop down list
Command Type - Text (SQL Script) ----------->
Click ok ----------->
Select query Designer Option and Add Tables icon on the rare right and Select Tables
(Or)
Write the query in SSMS and copy and paste in Query Designer
Select product Category.
Product Category ID, Select product Category.
Product Category Name, Product.
Product ID,Product.
Standard Cost, Product.
Size, Product.
Sell start Date, Product.
Reorder point From product category Inner Join
product sub category.
Product category ID = Product sub category. Product category ID.
Inner Join product on product. Product product sub category.
Product = Sub category ID Sub category ID
Inner Join Product cost History ON Product.
Product ID = Product cost History.
Product ID Provide the above query to the created data set and click Refresh ----------->
Click ok ----------->
Select View Menu and Select Data Set option or (Alt + Ctrl + D) ----------->
Select Layout tab to design the Report ----------->
Select tool box (or) (Alt + Ctrl + X) ----------->
Drag and Drop table Report item on body of the Report ----------->
Drag and Drop Category Name field on to Detail section of the table report [Building (or) ----------->
Mapping the field (or) Columns from data set to Report item] ----------->
Map other field as well ----------->
In table Report item Select 1 Row and 2 Row ----------->
Press F4 and Set ----------->
Border style - Solid ----------->
Select 1 Row [Header] and Press F4 for properties and set ----------->
Back ground color - Marron Text align - Centre Font Size - 9pt Color – White
Parameterized Report : In Parameterized Report the Report will be populated data based on the user input.
Steps to Configure Parameterized Report :
1.In previous Report example - In data tab add the following Where Clause to set the
Parameters
WHERE,
(Production. Product category. Product category ID = @ Product category ID)
2.In Data tab select New Data Set and Rename it as D set Product category ID Name
Data Source - D src product Details
Common Type - Text
Query String – Provide the following SQL command to populate all
The Product category ID, Name as well.
Select
Product category ID,
Name as category Name
From
Product. Product category.
3. Select Report Menu and Select Report parameters 4. Select Product category ID parameter and set,
Prompt – Select category
Check from Query Radio Button (to populate all ID from the Selected Data Set)
Data Set – D set product Category Id Name
Value field - Product category ID
Label field - category Name.
Click ok
Multi-Valued Parameterized Report: In Multi-Valued Parameterized Report the user Can Select multiple values from the Specified parameter.
Steps to Configure Multi-valued Report:
Expressions in SSRS:
1.Expression to format the Data using format function (or) format codes.
Select Standard cost text box ----------->
Right-click and select expression = Format (Fields ! standard cost. Value,” N 0 (Zero)”)
2.Expression to check must Records in the Specify field.
If any field value is Null then place - - Other Wise Display the actual Value of the field = IIF (IS Nothing (Fields Size. Value),)”- - ”, Field! Size. value ).
3.Expression to align the text:- If the text is Null then place (or) display the -- in Centre
Else left just field. Select Size text box Press F4 from properties and set Text Align - - IIS (IS Nothing (Fields Size. Value),” center”,” Left”
4.Expression to display the values in various colors based on the conditions Select size text box ----------->
Press F4 for properties ----------->
Set, ----------->
Color – =IIF (Fields! Size. Value < 39 ”RED ”, IIF (Fields! Size. Value > 30 ”BLUE ”, “Green”
(Blue - 79) -->(79) in Red color. In table Report item add one column and Rename the Header as Totals and provide The following expression = IIF((Field ! Standard Cost. Value- Field ! Re Order point . Value)) < 0, Replace ((Field ! Standard Cost. Value- Field ! Re Order point . Value),” -” ,”(“) & “)”, ((Field ! Standard Cost. Value- Field ! Re Order point . Value)
Select Totals text box (In Details section) ----------->
Press F4 properties ----------->
Set, Color - = ((Field ! Standard Cost. Value- Field !( Re Order point . Value)< 0 , “RED”, “Black”)
= IIF (Row number (nothing) mod 2, “silver”, ”Transparent”)
Chart: Chart Report item is used to display the data graphically as bass pie, column, area, and Other chart types.
In our previous Report example,
Drag and drop chart Report item after The table Report item. ----------->
Right Click on the chart and select Properties from the context menu.
In General tab, Title - = “Product Details for The month – “ & month Name (month (Field ! Sell start Date. Value)) Select Data tab ----------->
Data Set Name - Select D Set Product information from drop-down list value - Click Add to add column (or) Fields that would be plotted on Y-axis ----------->
In Edit chart Value, Series Label – standard cost value - = Field! standard cost value ----------->
Click ok - ----------->
Values – click Add to add column (or) fields that would be plotted on Y-axis ----------->
In Edit chart value ----------->
Series label – size ----------->
Value – Fields ! Size .Value ----------->
Click ok ----------->
Category Group – click Add to Add column (or) Fields that would be plotted on X-axis Select expression and provide the expression display the records like JAN – 01 Format, = Left (Month Name()(Month [ Fields ! sell start Date. value)).3 ) & “_” & Right (year (Fields ! sell start Date. value)).2) ----------->
Click ok twice ----------->
Select x-axis tab and set, Title – sell standard data ----------->
Check Major Gridline and specify the scale (or) interval 0.5 ----------->
Title Align – center ----------->
Major tick lines – cross ----------->
Major tick lines – cross ----------->
Check side Margins checkbox to display the chart items in center of the plotter area. ----------->
In Y-axis, Title – Size Check Major Gridline and specify the scale (or) interval 0.5 ----------->
Title Align – center ----------->
Major tick lines – cross ----------->
Major tick lines – cross ----------->
Check side Merging checkbox to display the chart items in center of the plotter area. In Legend Tab, Check show Legend (by default) Layout – Table Check legend style and set, font size - 8pz Click ok ----------->
How to set or fill custom color to the chart (series) ----------->
In data tab, Values – Select Standard cost and click edit ----------->
Select Appearance Tab ----------->
Click series style ----------->
Select Fill tab ----------->
Click Browse to select a color from the list ----------->
Select any color and click ok How to Setpoint labels in the chart (Display the exact Value on plotted area) ----------->
Values – Select Standard cost and click edit, ----------->
Select point labels Tab ----------->
Check show point labels checkpoint, ----------->
Data label - = “&” and Fields! standard cost. Value ----------->
Click ok
Rectangle : Rectangle is a Container Report item for other report items.
Steps to Configure rectangle report item
Scenario: Display Table report item in 1 page and matrix report item on another page. When You export it to excel or pdf etc..
Steps
Image: page report item is used to display a bit-mapped Image Suck as photos or Logos in a report. The Selected or Configured image will be embedded to the report or to the Selected project.
Scenario: Display Company Logo on each and every Report page.
Text Box: Text Box is used to display labels or fields or values calculated from expressions.
From our previous report example,
Drag and Drop text box report item on to page footer ----------->
Select Text Box and right click and select expression option ; compose the following expression = “page ” & Globals ! Page Number + “of ” & Globals ! Total pages = iif (Parameters ! Product category ID . Value = 1,”Bikes”,”components”) & Datails for the Month –“ & Month Name (Month (Field ! Sell Start Date.Value))
List Report Item: It is used to display a set of Report item that are Repeated for each group (or) Row of Data.
Steps to Configure List Report item :
Matrix: Matrix report item displays the aggregated data in a grid format that has a Variable number Of rows and a variable number of Columns as well .
Steps to Configure Matrix Report Item
Drill Down Report :
How to display Group totals or Subtotals : From the above table report example add the below expression at the group footer Under Standard cast header. = Sum ( Fields ! Standard cost. Value)
How to display Group totals of the report: From the above table report item, Provide the following expression to display the grand total Of the entire report. In report footer area Under Standard cost. = Sum (Fields ! Standard cost. Value)
How to Create template Reports in SSRS: Design basic Report template [Add a logo on header and display page numbers in page footer] ----------->
Rename it as Vijay IT Template, RDL ----------->
Go to Project path ----------->
Copy Vijay IT Template ,RDL and paste it in the specified (or) bellow mentioned path ----------->
C:/ Program Files / Microsoft visual studio 8 / common 7 / IDE / Private Assemblies / Project Assemblies / Project Items / Report project .
Linked Reports: Linked Report is the report with the settings and properties of its own. A linked report can have its own Name Description, Location, Parameter properties, Report Execution properties, etc..
Note: A linked report must use data source properties and layout of its base report .
Steps to Configure Linked Report:
Note: In properties of the Linked Report, Data Source, edit, and update option are not Available. Since linked report always points to the data source and layout of its base or its Parent report.
Sub Report: Sub Report item is used to display am embedded report within current report.
Steps to Configure Subreport : Open Business Intelligence Development Studio ----------->
Create a new report and rename it as Master Report .rdl ----------->
Create a new dataset and provide the following query Select * ----------->
From product category, INNER JOIN Product Subcategory ON Product category. Product category ID = Product Subcategory. Product category ID WHERE (Product category. Product category ID = @ Product category ID) ----------->
Create dataset 2 and provide the following Query Select Product category Id, Name As category Name from production. Product category ----------->
Select Report Menu and Select Report parameters ----------->
Set, ----------->
Select Product category ID parameter, Name - Product category Id Data type – String Prompt – Select Category ----------->
Select “From Query” radio button to populate all the values in a parameter drop-down list and set,
Data set – Data set 2 Value Field - Product category Id Label Field - Category Name ----------->
Click ok ----------->
In Layout Tab, Drag and Drop sub-report item ----------->
Select Sub report , right-click and Select properties ----------->
In General Tab, ----------->
Sub Report – select any report from the drop-down list (The DDL contains only the reports From the current projects) à In parameter Tab, ----------->
Parameter Name - Product category Id. ----------->
Parameter Value - = Parameters ! Product category ID value ----------->
Click ok ----------->
Select preview Tab
Ever the value it may be, either Original value or dummy value. The dummy value must Be like = “Bikes”.
Globals: Global parameters or fields applies to a set of variables built into reporting Services, Details While configuring report Service in SQL Server Configuration Tools Path – Provide path in Uniform Naming Convention format like //c or pc / Rama Credentials – provide User Name and password (given by the client) Render format – Select PDF or Excel Click Select Schedule to specify a time to execute the report. ----------->
Select once Radio button and specify the time when to execute the report ----------->
Click ok ----------->
Select parameter Values ----------->
Click ok The Report Server uses two databases to store the data after Successful completion of report configuration.
Like, report definition (pdf file details) report model, data sources, Schedule information, Security details.
Data like user Sessions, Cache information.
Expression to Show and Hide a column based on Parameter: Select any table column Press F4 for properties of the Selected table Column and set, Expand visibility Hidden – Select Expression = IIF (Parameters ! product Category ID . Value = 1, false, True)
How to provide Role-Based security on Report Manager :
Report Data Model :
At this point, the report model is available but it is not yet available to the end-user . Once we publish or deploy a report model to the Report Server, So that it will be available at the report Builder.
Report Builder: Report Builder is a windows application. Report Builder will load With the new report dialogue box and shows all the available report models.
Publish a report from the Report Builder :
Drill through Reports (or) Hyperlink Reports (or) Navigation Reports: Jump To Report:
Jump to Url:
Report Parameters properties :
Hidden: By using hidden properties we can hide the parameter on the parameter input area of the published Report .
For example: While implementing subreport hide the parameter in the embedded Report.
Multivalue: If you want to display multiple elections then multi-value parameter is used. In order to implement multi-value parameter Report IN operator must be used in the dataset. Internal: If you Set a parameter to internal it is not exposed in any way except in the Report Definition. The internal parameter is a parameter that can’t be changed at Runtime. A consumer of a published report will never see this as a parameter .
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
